package util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class ExcelUtil {

	// public AgentModel readUserCredentialExcelFile(File excelFile, String
	// agentId) throws Exception {
	//
	// /** Creating Input Stream* */
	// String appRoot = System.getProperty("webapp.root");
	// System.out.println("Application Root:" + appRoot);
	// FileInputStream input = new FileInputStream(excelFile);
	//
	// /** Create a POIFSFileSystem object* */
	// POIFSFileSystem fileSystem = new POIFSFileSystem(input);
	//
	// /** Create a workbook using the File System* */
	// HSSFWorkbook premiumrateWorkBook = new HSSFWorkbook(fileSystem);
	//
	// /** Get the first sheet from workbook* */
	// HSSFSheet sheet = premiumrateWorkBook.getSheet("Sheet1");
	// AgentModel model = readUserCredentials(sheet, agentId);
	// return model;
	// }

	// private AgentModel readUserCredentials(HSSFSheet sheet, String agentId) {
	// /** We now need something to iterate through the cells.* */
	// Iterator rowIter = sheet.rowIterator();
	//
	// /** Pass first row */
	// rowIter.next();
	//
	// AgentModel model = null;
	// while (rowIter.hasNext()) {
	// HSSFRow myRow = (HSSFRow) rowIter.next();
	// Iterator cellIter = myRow.cellIterator();
	// // move to age cell
	// if (!cellIter.hasNext())
	// break;
	// HSSFCell cell = (HSSFCell) cellIter.next();
	// int iac_no = (int) cell.getNumericCellValue();
	// System.out.println(iac_no);
	//
	// if (agentId.equals(String.valueOf(iac_no))) {
	// if (!cellIter.hasNext())
	// break;
	// cell = (HSSFCell) cellIter.next();
	// String agentName = cell.getStringCellValue();
	// // move to female cell
	// if (!cellIter.hasNext())
	// break;
	// cell = (HSSFCell) cellIter.next();
	// String branch = cell.getStringCellValue();
	// //
	// if (!cellIter.hasNext())
	// break;
	// cell = (HSSFCell) cellIter.next();
	// String pwd = "";
	// if (cell.getCellType() == 0) {
	// pwd = String.valueOf((int) cell.getNumericCellValue());//
	// cell.getNumericCellValue());
	// } else if (cell.getCellType() == 1) {
	// pwd = String.valueOf(cell.getStringCellValue());
	// }
	// model = new AgentModel();
	// model.setAgentName(agentName);
	// model.setAgentId(String.valueOf(iac_no));
	// model.setPassword(pwd);
	// model.setAddress1(branch);
	// return model;
	// // ULPremiumRate uLPremiumRate = new ULPremiumRate
	// // (age, rateOfMale, rateOfFemale);
	// }
	// // move to male cell
	//
	// }
	//
	// return null;
	// }

	public boolean updateUserPassword(File excelFile, String agentId, String pasword) throws Exception {

		// http://stackoverflow.com/questions/521274/edit-existing-excel-files-using-jxl-api-apache-poi

		/** Creating Input Stream* */
		String appRoot = System.getProperty("webapp.root");
		System.out.println("Application Root:" + appRoot);
		FileInputStream input = new FileInputStream(excelFile);

		/** Create a POIFSFileSystem object* */
		POIFSFileSystem fileSystem = new POIFSFileSystem(input);

		/** Create a workbook using the File System* */
		HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);

		/** Get the first sheet from workbook* */
		HSSFSheet sheet = workBook.getSheet("Sheet1");

		boolean res = updateUserPassword(sheet, agentId, pasword);

		input.close();

		if (res) {
			FileOutputStream fileOut = new FileOutputStream(excelFile);
			workBook.write(fileOut);
			fileOut.close();
		}

		return res;
	}

	private boolean updateUserPassword(HSSFSheet sheet, String agentId, String pasword) {

		boolean res = false;

		/** We now need something to iterate through the cells.* */
		Iterator rowIter = sheet.rowIterator();

		/** Pass first row */
		rowIter.next();

		while (rowIter.hasNext()) {
			HSSFRow myRow = (HSSFRow) rowIter.next();
			Iterator cellIter = myRow.cellIterator();

			// move to age cell
			if (!cellIter.hasNext()) {
				break;
			}

			HSSFCell cell = (HSSFCell) cellIter.next();
			int iac_no = (int) cell.getNumericCellValue();
			System.out.println(iac_no);

			if (agentId.equals(String.valueOf(iac_no))) {

				// move to name cell
				if (!cellIter.hasNext()) {
					break;
				}
				cell = (HSSFCell) cellIter.next();

				// move to branch cell
				if (!cellIter.hasNext()) {
					break;
				}
				cell = (HSSFCell) cellIter.next();

				// move to password cell
				if (!cellIter.hasNext()) {
					break;
				}
				cell = (HSSFCell) cellIter.next();

				// set password
				cell.setCellValue(pasword);

				res = true;
			}
		}

		return res;
	}
	
		
	
}
